<%@Page Language="VB"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>
<%@ Register TagPrefix="wrox" TagName="getdataset" Src="..\global\get-dataset-control.ascx" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Displaying Nested Relational Data</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Displaying Nested Relational Data</span><hr />
<!--------------------------------------------------------------------------->

<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>

<%'-- insert the control that creates the DataSet --%>
<wrox:getdataset id="ctlDataSet" runat="server"/>

<div id="divResults" runat="server"></div>

<script language="vb" runat="server">

Sub Page_Load()

   'get connection string from ..\global\connect-strings.ascx user control
   Dim strConnect = ctlConnectStrings.OLEDBConnectionString

   'create a variable to hold an instance of a DataSet object
   Dim objDataSet As DataSet

   'get dataset from get-dataset-control.ascx user control
   objDataSet = ctlDataSet.BooksDataSet(strConnect, "ISBN LIKE '18610014%'")

   If IsNothing(objDataSet) Then Exit Sub

   'now we're ready to display the contents of the DataSet object
   'create a string to hold the results
   Dim strResult As String = ""

   'create a reference to our main Books table in the DataSet
   Dim objTable As DataTable = objDataSet.Tables("Books")

   'create references to each of the relationship objects in the DataSet
   Dim objAuthorRelation As DataRelation = objTable.ChildRelations("BookAuthors")
   Dim objPriceRelation As DataRelation = objTable.ChildRelations("BookPrices")

   'now we can iterate through the rows in the Books table
   Dim objRow, objChildRow As DataRow
   For Each objRow In objTable.Rows

      'get the book details and append them to the "results" string
      strResult += "<b>" & objRow("Title") & "</b><br />&nbsp; ISBN: " & objRow("ISBN") _
         & " &nbsp; Release Date: " & FormatDateTime(objRow("PublicationDate"), 1) & "<br />"

      'get a collection (array) of all the matching Author table rows for this row
      Dim colChildRows() As DataRow = objRow.GetChildRows(objAuthorRelation)

      strResult += "&nbsp; Author(s): "

      'iterate through all the matching Author records adding to the result string
      For Each objChildRow In colChildRows
         strResult += objChildRow("FirstName") & " " & objChildRow("LastName") & ", "
      Next
      strResult += "<br />"

      'repeat using the Price table relationship to display data from matching Price records
      colChildRows = objRow.GetChildRows(objPriceRelation)
      strResult += "&nbsp; Price: "
      For Each objChildRow In colChildRows
         strResult += objChildRow("Currency") & ":" & objChildRow("Price") & " &nbsp; "
      Next
      strResult += "<p />"

   Next  'and repeat for next row in Books table

   divResults.innerHTML = strResult  'display the results

End Sub
</script>


<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
